package com.training.dao;

import com.training.util.JdbcTemplate;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class ProductDao {
    private final JdbcTemplate jdbcTemplate = new JdbcTemplate();
    
    // 获取所有产品
    public List<Map<String, Object>> findAll() throws SQLException {
        String sql = "SELECT id, name, price, stock, created_at, updated_at FROM products ORDER BY id";
        return jdbcTemplate.queryForList(sql);
    }
    
    // 根据ID获取产品
    public Map<String, Object> findById(int id) throws SQLException {
        String sql = "SELECT id, name, price, stock, created_at, updated_at FROM products WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, id);
    }
    
    // 创建新产品
    public Map<String, Object> create(String name, double price, int stock) throws SQLException {
        String sql = "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)";
        int generatedId = jdbcTemplate.insertAndGetKey(sql, name, price, stock);
        return findById(generatedId);
    }
    
    // 更新产品
    public boolean update(int id, String name, double price, int stock) throws SQLException {
        String sql = "UPDATE products SET name = ?, price = ?, stock = ? WHERE id = ?";
        int rowsAffected = jdbcTemplate.update(sql, name, price, stock, id);
        return rowsAffected > 0;
    }
    
    // 删除产品
    public boolean deleteById(int id) throws SQLException {
        String sql = "DELETE FROM products WHERE id = ?";
        int rowsAffected = jdbcTemplate.update(sql, id);
        return rowsAffected > 0;
    }
    
    // 分页查询产品
    public List<Map<String, Object>> findWithPagination(int offset, int limit, String sortBy, String search) throws SQLException {
        StringBuilder sql = new StringBuilder("SELECT id, name, price, stock, created_at, updated_at FROM products");
        
        // 搜索条件
        if (search != null && !search.trim().isEmpty()) {
            sql.append(" WHERE name LIKE ?");
        }
        
        // 排序
        if ("price".equals(sortBy)) {
            sql.append(" ORDER BY price ASC");
        } else if ("name".equals(sortBy)) {
            sql.append(" ORDER BY name ASC");
        } else {
            sql.append(" ORDER BY id ASC");
        }
        
        // 分页
        sql.append(" LIMIT ? OFFSET ?");
        
        if (search != null && !search.trim().isEmpty()) {
            return jdbcTemplate.queryForList(sql.toString(), "%" + search + "%", limit, offset);
        } else {
            return jdbcTemplate.queryForList(sql.toString(), limit, offset);
        }
    }
    
    // 获取总数
    public int getTotalCount(String search) throws SQLException {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM products");
        
        if (search != null && !search.trim().isEmpty()) {
            sql.append(" WHERE name LIKE ?");
            Object result = jdbcTemplate.queryForValue(sql.toString(), "%" + search + "%");
            return ((Number) result).intValue();
        } else {
            Object result = jdbcTemplate.queryForValue(sql.toString());
            return ((Number) result).intValue();
        }
    }
    
    // 检查产品是否存在
    public boolean existsById(int id) throws SQLException {
        String sql = "SELECT COUNT(*) FROM products WHERE id = ?";
        Object result = jdbcTemplate.queryForValue(sql, id);
        return ((Number) result).intValue() > 0;
    }
}
